package com.whzykj.model;

import java.util.List;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Record;
import com.whzykj.jfinal.TableBind;

@TableBind(name = "goodsinfo", pk = "goods_id")
public class GoodsInfo extends Model<GoodsInfo> {
	private static final long serialVersionUID = 1L;
	public static final GoodsInfo me = new GoodsInfo();
	
	//查询订单列表
	public List<Record> getGoodsInfolist(String station,String starttime,String endtime)
	{
	   StringBuffer sbf=new StringBuffer();
	   sbf.append(" SELECT c.GOODS_ID,c.GOODS_NAME,c.GOODS_kind,goods_packing,(SUM(b.SALE_NUM) * c.goods_spec) weight,SUM(b.SALE_NUM) sale_num,SUM(b.ORIGINAL_PRICE*b.SALE_NUM) ORIGINAL_PRICE,SUM(b.SALE_PRICE*b.SALE_NUM) sale_price, ");
	   sbf.append(" (SUM(b.ORIGINAL_PRICE*b.SALE_NUM) - SUM(b.SALE_PRICE*b.SALE_NUM)) un_price  ");
	   sbf.append(" ,(SELECT st.store_num FROM store st WHERE st.goods_id = c.GOODS_ID AND DATE(st.set_date) = CURDATE()) store_num ");
	   sbf.append(" FROM orderinfo a,orderdetails b,goodsinfo c,customer u WHERE a.cust_id=u.`CUST_ID` AND a.`ORDER_ID`=b.`ORDER_ID` AND b.`GOODS_ID`=c.`GOODS_ID` AND a.`STATUS`<>'over'  ");
	   if (station != null && !"".equals(station) && !"-1".equals(station)) {
			sbf.append(" and u.station_id=" + station);
	   }
	   if(starttime != null && !"".equals(starttime) && endtime != null && !"".equals(endtime))
	   {
		   sbf.append(" and a.order_date >= '"+starttime+" 00:00:00' and a.order_date <= '"+endtime+" 23:59:59' ");
	   }
	   sbf.append("  ");
	   sbf.append(" GROUP BY goods_id ORDER BY goods_id ");
	   return Db.find(sbf.toString());
	}
	
	/**
	 * 查询菜品最新库存信息
	 * @return
	 */
	public List<Store> findLatestStoreList(){
		String sql="select * from store where TO_DAYS(SET_DATE)=(select MAX(TO_DAYS(SET_DATE)) from store) ";
		List<Store> list=Store.me.find(sql);
		return list;
	}
	
	/**
	 * 查询单一商品的最新库存信息
	 * @param goodsid
	 * @param storeList
	 * @return
	 */
	public Store findLatestStoreByGoodsid(Integer goodsid,List<Store> storeList){
		Store store=null;
		if(storeList!=null && storeList.size()>0){
			for(Store s:storeList){
				if(s.getInt("GOODS_ID")==goodsid){
					store=s;
					break;
				}
			}
		}
		return store;
	}
	
	/**
	 * 查询最新商品的价格信息
	 * @param stationid
	 * @param goodsName
	 * @return
	 */
	public List<Price> findLatestPriceList(String stationid,String goodsName){
		StringBuffer sb=new StringBuffer(" SELECT p.* from price p ");
		sb.append(" INNER JOIN goodsinfo g on p.goods_id=g.goods_id");
		sb.append(" where TO_DAYS(p.SET_DATE)=(select MAX(TO_DAYS(pr.SET_DATE)) from price pr INNER JOIN goodsinfo gi on pr.goods_id=gi.GOODS_ID WHERE 1=1 ");
		if(stationid==null || stationid.equals("")){
			sb.append(" and pr.STATION_ID=-1");
		}else{
			sb.append(" and pr.STATION_ID="+stationid);
		}
		if(goodsName!=null && !goodsName.equals("")){
			sb.append("and gi.goods_name LIKE '%"+goodsName+"%'");
		}
		sb.append(") ");
		if(stationid==null || stationid.equals("")){
			sb.append(" and p.STATION_ID=-1");
		}else{
			sb.append(" and p.STATION_ID="+stationid);
		}
		if(goodsName!=null && !goodsName.equals("")){
			sb.append("and g.goods_name LIKE '%"+goodsName+"%'");
		}
		return Price.me.find(sb.toString());
	}
	
	/**
	 * 查找单一商品的最新价格信息
	 * @param goodsid
	 * @param list
	 * @return
	 */
	public Price findLatestPriceByGoodsid(Integer goodsid,List<Price> list){
		Price price=null;
		if(list!=null && list.size()>0){
			for(Price p:list){
				if(p.getInt("GOODS_ID")==goodsid){
					price=p;
					break;
				}
			}
		}
		return price;
	}
}
